package com.tianpengcc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.tianpengcc.dao.DirayDAO;
import com.tianpengcc.model.DateType;
import com.tianpengcc.model.Diray;
import com.tianpengcc.model.DirayType;
import com.tianpengcc.model.PageBean;
import com.tianpengcc.util.DBUtil;
import com.tianpengcc.util.DateUtil;

public class DirayDAOImpl implements DirayDAO{
	private Connection conn ;
	
	@Override
	public List<Diray> findByPage(PageBean<Diray> pageBean) throws Exception {
		if(pageBean == null){
			return null ;
		}
		if(pageBean.getPageNumber() == 0){
			return null ;
		}
		if(pageBean.getPageSize() == 0){
			return null ;
		}
		this.conn = DBUtil.getConnection() ;
		List<Diray> list = new ArrayList<Diray>();
		String sql = "SELECT * FROM t_diray ORDER BY releaseTime DESC LIMIT ?,?" ;
		PreparedStatement ppst = conn.prepareStatement(sql);
		ppst.setInt(1,pageBean.getStartIndex()) ;
		ppst.setInt(2, pageBean.getPageSize());
		ResultSet rs = ppst.executeQuery();
		Diray diray = null ;
		while(rs.next()){
			diray = new Diray() ;
			diray.setDirayId(rs.getInt(1));
			diray.setDirayName(rs.getString(2));
			diray.setContent(rs.getString(3));
			diray.setReleaseTime(new Date(rs.getDate(4).getTime()));
			DirayType dirayType = new DirayType() ;
			dirayType.setDirayTypeId(rs.getInt(5));
			diray.setDirayType(dirayType);
			list.add(diray) ;
		}
		rs.close(); 
		ppst.close();
		DBUtil.close(conn);
		return list;
	}

	@Override
	public int findTotalCount() throws Exception {
		this.conn = DBUtil.getConnection() ;
		int total = -1 ;
		String sql = "SELECT COUNT(*) FROM t_diray ;" ;
		Statement statement = conn.createStatement();
		ResultSet rs = statement.executeQuery(sql);
 		if(rs.next()){
 			total = rs.getInt(1) ;
 		}
		rs.close();
 		statement.close();
		conn.close();
		return total;
	}

	@Override
	public List<DateType> findDateTypeByPageBean(int dateTypeSize)
			throws Exception {
		this.conn = DBUtil.getConnection() ;
		
		List<DateType> list = new ArrayList<DateType>();
		StringBuffer sb = new StringBuffer() ;
		sb.append("SELECT") ;
		sb.append(" YEAR(releaseTime) AS ye,") ;
		sb.append(" MONTH(releaseTime) AS mo ,") ;
		sb.append(" COUNT(*) AS total") ;
		sb.append(" FROM t_diray ") ;
		sb.append(" GROUP BY YEAR(releaseTime),MONTH(releaseTime)") ;
		sb.append(" ORDER BY releaseTime DESC") ;
		sb.append(" LIMIT 0,?") ;
		
		PreparedStatement ppst = conn.prepareStatement(sb.toString());
		ppst.setInt(1,dateTypeSize) ;
		ResultSet rs = ppst.executeQuery();
		DateType dateType = null ;
		while(rs.next()){
			dateType = new DateType() ;
			dateType.setYear(rs.getString(1)) ;
			dateType.setMonth(rs.getString(2));
			dateType.setTotal(rs.getInt(3));
			list.add(dateType) ;
		}
		rs.close(); 
		ppst.close();
		
		DBUtil.close(conn);
		return list;
	}

	@Override
	public List<Diray> findByDirayTypeAndPage(Integer dirayTypeId,
			PageBean<Diray> pageBean) throws Exception {
		List<Diray> list = new ArrayList<Diray>();
		this.conn = DBUtil.getConnection() ;
		StringBuffer sb = new StringBuffer() ;
		sb.append("SELECT") ;
		sb.append(" d.*,") ;
		sb.append(" dt.dirayTypeName") ;
		sb.append(" FROM t_diray d,t_diraytype dt ") ;
		sb.append(" WHERE d.dirayTypeId = dt.dirayTypeId AND dt.dirayTypeId = ?") ;
		sb.append(" LIMIT ?,?") ;
		PreparedStatement ppst = conn.prepareStatement(sb.toString());
		ppst.setInt(1, dirayTypeId);
		ppst.setInt(2, pageBean.getStartIndex());
		ppst.setInt(3, pageBean.getPageSize());
		ResultSet rs = ppst.executeQuery();
		Diray d = null ;
		DirayType dt = null ;
		while(rs.next()){
			d = new Diray() ;
			d.setDirayId(rs.getInt(1));
			d.setDirayName(rs.getString(2));
			d.setContent(rs.getString(3));
			d.setReleaseTime(DateUtil.formatString(rs.getString(4), "yyyy-MM-dd HH:mm:ss"));
			if(dt == null){
				String dirayTypeName = rs.getString(6);
				dt = new DirayType() ;
				dt.setDirayTypeId(dirayTypeId);
				dt.setDirayTypeName(dirayTypeName);
			}
			d.setDirayType(dt);
			list.add(d) ;
		}
		rs.close(); 
		ppst.close();
		DBUtil.close(conn);
		return list;
	}

	@Override
	public List<Diray> findByYearAndMonth(int year, int month,PageBean<Diray> pageBean) throws Exception {
		List<Diray> list = new ArrayList<Diray>();
		this.conn = DBUtil.getConnection() ;
		StringBuffer sb = new StringBuffer() ;
		sb.append("SELECT") ;
		sb.append(" d.*") ;
		sb.append(" FROM t_diray d") ;
		sb.append(" WHERE YEAR(d.releaseTime) = ? AND MONTH(d.releaseTime) = ?") ;
		sb.append(" LIMIT ?,?") ;
		
		PreparedStatement ppst = conn.prepareStatement(sb.toString());
		ppst.setInt(1, year);
		ppst.setInt(2, month);
		ppst.setInt(3, pageBean.getStartIndex());
		ppst.setInt(4, pageBean.getPageSize());
		
		ResultSet rs = ppst.executeQuery();
		Diray d = null ;
		while(rs.next()){
			d = new Diray() ;
			d.setDirayId(rs.getInt(1));
			d.setDirayName(rs.getString(2));
			d.setContent(rs.getString(3));
			d.setReleaseTime(DateUtil.formatString(rs.getString(4), "yyyy-MM-dd HH:mm:ss"));
			DirayType dt = new DirayType() ;
			dt.setDirayTypeId(rs.getInt(5));
			d.setDirayType(dt);
			list.add(d) ;
		}
		rs.close(); 
		ppst.close();
		DBUtil.close(conn);
		return list;
	}

	@Override
	public int findCountByYearAndMonth(int year, int month) throws Exception {
		this.conn = DBUtil.getConnection() ;
		int total = -1 ;
		StringBuffer sb = new StringBuffer() ;
		sb.append("SELECT") ;
		sb.append(" COUNT(*)") ;
		sb.append(" FROM t_diray d") ;
		sb.append(" WHERE YEAR(d.releaseTime) = ? AND MONTH(d.releaseTime) = ?") ;
		PreparedStatement ppst = conn.prepareStatement(sb.toString());
 		ppst.setInt(1, year);
 		ppst.setInt(2, month);
		ResultSet rs = ppst.executeQuery();
 		if(rs.next()){
 			total = rs.getInt(1) ;
 		}
		
		rs.close();
 		ppst.close();
		conn.close();
		return total;
	}

	@Override
	public List<Diray> findByDirayNameLikeAndPage(String dirayName,
			PageBean<Diray> pageBean) throws Exception {
		if(pageBean == null){
			return null ;
		}
		if(pageBean.getPageNumber() == 0){
			return null ;
		}
		if(pageBean.getPageSize() == 0){
			return null ;
		}
		this.conn = DBUtil.getConnection() ;
		List<Diray> list = new ArrayList<Diray>();
		StringBuffer sb = new StringBuffer() ;
		sb.append("SELECT") ;
		sb.append(" *") ;
		sb.append(" FROM t_diray ") ;
		sb.append(" WHERE dirayName LIKE ?") ; //需要封装
		sb.append(" LIMIT ?,?") ;
		PreparedStatement ppst = conn.prepareStatement(sb.toString());
		StringBuffer dirayNameBuffer = new StringBuffer() ;
		dirayNameBuffer.append("%") ;
		dirayNameBuffer.append(dirayName) ;
		dirayNameBuffer.append("%") ;
		
		ppst.setString(1, dirayNameBuffer.toString());
		ppst.setInt(2,pageBean.getStartIndex()) ;
		ppst.setInt(3, pageBean.getPageSize());
		ResultSet rs = ppst.executeQuery();
		Diray diray = null ;
		while(rs.next()){
			diray = new Diray() ;
			diray.setDirayId(rs.getInt(1));
			diray.setDirayName(rs.getString(2));
			diray.setContent(rs.getString(3));
			diray.setReleaseTime(new Date(rs.getDate(4).getTime()));
			DirayType dirayType = new DirayType() ;
			dirayType.setDirayTypeId(rs.getInt(5));
			diray.setDirayType(dirayType);
			list.add(diray) ;
		}
		rs.close(); 
		ppst.close();
		DBUtil.close(conn);
		return list;
	}

	@Override
	public int findCountByDirayNameLike(String dirayName) throws Exception {
		this.conn = DBUtil.getConnection() ;
		int total = -1 ;
		StringBuffer sb = new StringBuffer() ;
		sb.append("SELECT") ;
		sb.append(" count(*)") ;
		sb.append(" FROM t_diray ") ;
		sb.append(" WHERE dirayName LIKE ?") ; //需要封装
		
		StringBuffer dirayNameBuffer = new StringBuffer() ;
		dirayNameBuffer.append("%") ;
		dirayNameBuffer.append(dirayName) ;
		dirayNameBuffer.append("%") ;
		PreparedStatement ppst = conn.prepareStatement(sb.toString());
		ppst.setString(1, dirayNameBuffer.toString());
		ResultSet rs = ppst.executeQuery();
		if(rs.next()){
 			total = rs.getInt(1) ;
 		}
		rs.close();
 		ppst.close();
		conn.close();
		return total;
	}

	@Override
	public Diray findByDirayId(Integer dirayId) throws Exception {
		this.conn = DBUtil.getConnection() ;
		StringBuffer sb = new StringBuffer() ;
		sb.append("SELECT") ;
		sb.append(" d.*,") ;
		sb.append(" dt.dirayTypeName") ;
		sb.append(" FROM t_diray d,t_diraytype dt ") ;
		sb.append(" WHERE d.dirayTypeId = dt.dirayTypeId AND d.dirayId = ?") ;
		PreparedStatement ppst = conn.prepareStatement(sb.toString());
		ppst.setInt(1, dirayId);
		ResultSet rs = ppst.executeQuery();
		Diray d = null ;
		DirayType dt = null ;
		if(rs.next()){
			d = new Diray() ;
			d.setDirayId(rs.getInt(1));
			d.setDirayName(rs.getString(2));
			d.setContent(rs.getString(3));
			d.setReleaseTime(DateUtil.formatString(rs.getString(4), "yyyy-MM-dd HH:mm:ss"));
			if(dt == null){
				String dirayTypeName = rs.getString(6);
				dt = new DirayType() ;
				dt.setDirayTypeId(rs.getInt(5));
				dt.setDirayTypeName(dirayTypeName);
			}
			d.setDirayType(dt);
		}
		rs.close(); 
		ppst.close();
		DBUtil.close(conn);
		return d;
	}

	@Override
	public Diray doCreate(Diray diray) throws Exception {
		Diray new_diray = null ;
		this.conn = DBUtil.getConnection() ;
		String sql = "INSERT INTO t_diray VALUES(NULL,?,?,NOW(),?) ;" ;
		PreparedStatement ppst = conn.prepareStatement(sql);
 		ppst.setString(1, diray.getDirayName());
 		ppst.setString(2, diray.getContent());
 		ppst.setInt(3, diray.getDirayType().getDirayTypeId());
		int record = ppst.executeUpdate();
 		if(record > 0){
 			PageBean<Diray> pageBean = new PageBean<Diray>(1,1) ;
 			List<Diray> list = this.findByDirayNameLikeAndPage(diray.getDirayName(), pageBean);
 			new_diray = list.get(0) ;
 		}else{
 			throw new Exception("日记未被保存") ;
 		}
		
 		ppst.close();
		DBUtil.close(conn);
		return new_diray ;
	}

	@Override
	public Diray doUpdate(Integer dirayId, Diray diray) throws Exception {
		Diray new_diray = null ;
		this.conn = DBUtil.getConnection() ;
		String sql = "UPDATE t_diray SET dirayName = ?,content = ?,dirayTypeId=? WHERE dirayId = ?;" ;
		PreparedStatement ppst = conn.prepareStatement(sql);
 		ppst.setString(1, diray.getDirayName());
 		ppst.setString(2, diray.getContent());
 		ppst.setInt(3, diray.getDirayType().getDirayTypeId());
 		ppst.setInt(4, dirayId);
		int record = ppst.executeUpdate();
 		if(record > 0){
 			new_diray = this.findByDirayId(dirayId) ;
 		}else{
 			throw new Exception("日记未被保存") ;
 		}
		
		DBUtil.close(conn);
		return new_diray ;
	}

	@Override
	public Diray doUpdate(Diray diray) throws Exception {
		Diray new_diray = null ;
		if(diray.getDirayId() == null || diray.getDirayId() == 0){
			throw new Exception("更新失败") ;
		}else{
			new_diray = doUpdate(diray.getDirayId(),diray) ;
		}
		return new_diray;
	}

	@Override
	public void doDelete(Integer dirayId) throws Exception {
		this.conn = DBUtil.getConnection() ;
		String sql = "DELETE FROM t_diray WHERE dirayId = ?" ;
		PreparedStatement ppst = conn.prepareStatement(sql);
 		ppst.setInt(1, dirayId);
		int record = ppst.executeUpdate();
 		if(record <= 0){
 			throw new Exception("日记未被删除") ;
 		}
 		
 		ppst.close();
		DBUtil.close(conn);
	}

}
